package com.ssh.dao;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.SessionFactory;

import com.ssh.entity.Staff;
import com.ssh.model.pdata.CSVWmatePData;
import com.ssh.model.pdata.InnerDiverMPData;
import com.ssh.model.pdata.StaffMPData;

public class StaffDAOImpl implements StaffDAOI {

	private SessionFactory sessionFactory;

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.login.StaffDAO#addStaff(com.ssh.entity.Staff)
	 */
	@Override
	public Staff addStaff(Staff staff) {
		// TODO Auto-generated method stub
		try {
			sessionFactory.getCurrentSession().save(staff);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return staff;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.login.StaffDAO#addStaff(com.ssh.entity.Staff[])
	 */
	@Override
	public int addStaff(Staff... staffs) {
		// TODO Auto-generated method stub
		return 0;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.login.StaffDAO#deleteStaff(java.lang.String[])
	 */
	@Override
	public int deleteStaff(String... strings) {
		// TODO Auto-generated method stub
		return 0;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.login.StaffDAO#deleteStaffByStaffId(java.lang.String)
	 */
	@Override
	public int deleteStaffByStaffId(String staffId) {
		// TODO Auto-generated method stub
		try {
			String hql = "delete Staff as s where s.staffId= :staffId";
			return sessionFactory.getCurrentSession().createQuery(hql)
					.setParameter( "staffId", staffId).executeUpdate();
		} catch (Exception e) {
			return 0;
		}
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffDAOI#getAllStaff()
	 */
	@Override
	public List<StaffMPData> getAllStaffMPData() {
		// TODO Auto-generated method stub
    	String hql = "select new com.ssh.model.pdata.StaffMPData(s.address, s.birthday, p.departmentName,"
				+ "s.email, s.firstName, s.lastName, s.marriage,"
				+ "s.sex, s.staffId, s.staffTel) from Staff as s ,Position as p "
				+ "where  s.positionPositionId=p.positionId";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		@SuppressWarnings("unchecked")
		List<StaffMPData> staffMPData = query.list();
		return staffMPData;
	}

	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffDAOI#queryByDep(java.lang.String)
	 */
	@Override
	public List<StaffMPData> queryByDep(String dep) {
		// TODO Auto-generated method stub
		String hql = "select new com.ssh.model.pdata.StaffMPData(s.address, s.birthday, p.departmentName,"
				+ "s.email, s.firstName, s.lastName, s.marriage,"
				+ "s.sex, s.staffId, s.staffTel) from Staff as s ,Position as p "
				+ "where p.departmentName = ? and s.positionPositionId=p.positionId";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setParameter(0, dep);
		@SuppressWarnings("unchecked")
		List<StaffMPData> staffList = query.list();
		return staffList;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffDAOI#queryBySex(java.lang.String)
	 */
	@Override
	public List<StaffMPData> queryBySex(String sex) {
		// TODO Auto-generated method stub
		String hql = "select new com.ssh.model.pdata.StaffMPData(s.address, s.birthday, p.departmentName,"
				+ "s.email, s.firstName, s.lastName, s.marriage,"
				+ "s.sex, s.staffId, s.staffTel) from Staff as s ,Position as p "
				+ "where s.sex = ? and s.positionPositionId=p.positionId";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, sex);
		@SuppressWarnings("unchecked")
		List<StaffMPData> staffMPData = query.list();
		return staffMPData;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffDAOI#queryBySexDep(java.lang.String,
	 * java.lang.String)
	 */
	@Override
	public List<StaffMPData> queryBySexDep(String sex, String dep) {
		// TODO Auto-generated method stub
		// String hql =
		// "from Staff as s,Position as p where s."+param1+"=? and p."+param2+"=?".toString();
		String hql =  "select new com.ssh.model.pdata.StaffMPData(s.address, s.birthday, p.departmentName,"
				+ "s.email, s.firstName, s.lastName, s.marriage,"
				+ "s.sex, s.staffId, s.staffTel) from Staff as s ,Position as p "
				+ "where (s.sex = ? and p.departmentName= ?) and s.positionPositionId=p.positionId";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setString(0, sex);
		query.setString(1, dep);
		@SuppressWarnings("unchecked")
		List<StaffMPData> staffMPList = query.list();
		return staffMPList;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffDAOI#querySMPDataById(java.lang.String)
	 */
	@Override
	public StaffMPData querySMPDataById(String staffId) {
		// TODO Auto-generated method stub
		String hql =  "select new com.ssh.model.pdata.StaffMPData(s.address, s.birthday, p.departmentName,"
				+ "s.email, s.firstName, s.lastName, s.marriage,"
				+ "s.sex, s.staffId, s.staffTel) from Staff as s , Position as p "
				+ "where (s.staffId = :staffId and s.positionPositionId=p.positionId)";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setString("staffId", staffId);
		StaffMPData staffMPData = (StaffMPData) query.uniqueResult();
		return staffMPData;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.login.StaffDAO#queryStaff(java.lang.String[])
	 */
	@Override
	public List<Staff> queryStaff(String... strings) {
		// TODO Auto-generated method stub
		Query query = sessionFactory.getCurrentSession().createQuery(
				"from Staff as staff where staff.staffId = ?");
		query.setString(0, strings[0]);
		List<Staff> staffList = query.list();
		return staffList;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.StaffDAOI#queryStaffByExtra(java.lang.String)
	 */
	@Override
	public List<Staff> queryStaffByExtra(String extra) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public Staff queryStaffByStaffId(String staffId) {
		Staff staff ;
		try{
			Query query = sessionFactory.getCurrentSession().createQuery(
					"from Staff as staff where staff.staffId = ?");
			query.setString(0, staffId);
			staff=(Staff)query.uniqueResult();

		}
		catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return staff;
	}

	@Override
	public InnerDiverMPData queryInnerDById(String staffId) {
		InnerDiverMPData innerDiverMPData ;
		try{
			String hql ="select new com.ssh.model.pdata.InnerDiverMPData(id,staff) " +
					"from Staff as staff,InnerDriver id  " +
					"where staff.staffId =:staffId and id.staffId=staff.staffId";
			Query query = sessionFactory.getCurrentSession().createQuery(hql).setParameter("staffId", staffId);
			innerDiverMPData=(InnerDiverMPData)query.uniqueResult();

		}
		catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return innerDiverMPData;
	}

	@Override
	public List<InnerDiverMPData> queryAllInnerDriver() {
		List<InnerDiverMPData> innerDiverMPData ;
		try{
			String hql ="select new com.ssh.model.pdata.InnerDiverMPData(id,staff) " +
					"from Staff as staff,InnerDriver id  where id.staffId=staff.staffId";
			Query query = sessionFactory.getCurrentSession().createQuery(hql);
			innerDiverMPData=query.list();

		}
		catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return innerDiverMPData;
	}

	@Override
	public List<InnerDiverMPData> queryInnerDriverByState(String state) {
		List<InnerDiverMPData> innerDiverMPData ;
		try{
			String hql ="select new com.ssh.model.pdata.InnerDiverMPData(id,staff) " +
					"from Staff as staff,InnerDriver id  " +
					" where id.staffId=staff.staffId and id.state=:state";
			Query query = sessionFactory.getCurrentSession().createQuery(hql).setParameter("state", state);
			innerDiverMPData=query.list();

		}
		catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return innerDiverMPData;
	}

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see
	 * com.ssh.dao.login.StaffDAO#updateStaffByStaffId(com.ssh.entity.Staff)
	 */
	@Override
	public Staff updateStaff(Staff staff) {
		// TODO Auto-generated method stub
		try{
			sessionFactory.getCurrentSession().update(staff);
		}
		catch(Exception e){
			e.printStackTrace();
			return null;
		}

		return staff;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see com.ssh.dao.login.StaffDAO#updateStaff(com.ssh.entity.Staff[])
	 */
	@Override
	public int updateStaff(Staff... staffs) {
		// TODO Auto-generated method stub
		return 0;
	}

	/* (non-Javadoc)
	 * @see com.ssh.dao.StaffDAOI#queryCSVWDataById(java.lang.String)
	 */
	@Override
	public List<CSVWmatePData> queryCSVWDataById(String dimId) {
		// TODO Auto-generated method stub
		String hql = "select new com.ssh.model.pdata.CSVWmatePData(p,s) " +
				"from Staff as s, Position as p  " +
				"where  p.positionId=s.positionPositionId and s.s.staffId like '%:dimId%' ";
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setParameter("dimId", dimId);
		return query.list();
	}

	/* (non-Javadoc)
	 * @see com.ssh.dao.StaffDAOI#getAllWmatePData()
	 */
	@Override
	public List<CSVWmatePData> getAllWmatePData() {
		// TODO Auto-generated method stub
		String hql = "select new com.ssh.model.pdata.CSVWmatePData(p,s)" +
				"from Staff as s, Position as p " +
				"where p.positionId=s.positionPositionId " ;
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		return  query.list();
	}

	/* (non-Javadoc)
	 * @see com.ssh.dao.StaffDAOI#queryWMPDataByTel(java.lang.String)
	 */
	@Override
	public List<CSVWmatePData> queryWMPDataByTel(String dimTel) {
		// TODO Auto-generated method stub
		String hql = "select new com.ssh.model.pdata.CSVWmatePData(p,s)" +
				"from Staff as s, Position as p " +
				"where p.positionId=s.positionPositionId and s.s.staffTel like '%:dimTel%'" ;
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setParameter("dimTel", dimTel);
		return  query.list();
	}

	/* (non-Javadoc)
	 * @see com.ssh.dao.StaffDAOI#queryWMPDataByFName(java.lang.String)
	 */
	@Override
	public List<CSVWmatePData> queryWMPDataByFName(String dimFName) {
		// TODO Auto-generated method stub
		String hql = "select new com.ssh.model.pdata.CSVWmatePData(p,s)" +
				"from Staff as s, Position as p " +
				"where p.positionId=s.positionPositionId and s.s.firstName like '%:dimFName%'" ;
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setParameter("dimFName", dimFName);
		return  query.list();
	}

	/* (non-Javadoc)
	 * @see com.ssh.dao.StaffDAOI#queryWMPDataByLName(java.lang.String)
	 */
	@Override
	public List<CSVWmatePData> queryWMPDataByLName(String dimFLame) {
		// TODO Auto-generated method stub
		String hql = "select new com.ssh.model.pdata.CSVWmatePData(p,s)" +
				"from Staff as s, Position as p " +
				"where p.positionId=s.positionPositionId and s.s.lastName like '%:dimFLame%'" ;
		Query query = sessionFactory.getCurrentSession().createQuery(hql);
		query.setParameter("dimFLame", dimFLame);
		return  query.list();
	}

}
